CREATE TABLE [dbo].[EmailAccount] (
	[EmailAccount] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AccountPassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SmtpServer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LogDir] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmailListSize] [int] NULL ,
	[EmailSize] [int] NULL ,
	[State] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmailAddress] (
	[EmailID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NULL ,
	[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreateDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmailAndList] (
	[EmailListID] [int] NOT NULL ,
	[EmailID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmailList] (
	[EmailListID] [int] IDENTITY (1, 1) NOT NULL ,
	[ListName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UserID] [int] NULL ,
	[CreateDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[QueuedEmail] (
	[QueuedEmailID] [int] IDENTITY (1, 1) NOT NULL ,
	[UserID] [int] NULL ,
	[From] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[To] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Subject] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreateDate] [datetime] NULL ,
	[State] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[SentEmail] (
	[UserID] [int] NOT NULL ,
	[FromEmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ToEmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SentDate] [datetime] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EmailAddress] ADD 
	CONSTRAINT [PK_EmailAddress] PRIMARY KEY  CLUSTERED 
	(
		[EmailID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[EmailList] ADD 
	CONSTRAINT [PK_EmailList] PRIMARY KEY  CLUSTERED 
	(
		[EmailListID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[QueuedEmail] ADD 
	CONSTRAINT [PK_QueuedEmail] PRIMARY KEY  CLUSTERED 
	(
		[QueuedEmailID]
	)  ON [PRIMARY] 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DeleteEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DeleteEmail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DeleteEmailAndList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DeleteEmailAndList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DeleteEmailAndListByEmailListID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DeleteEmailAndListByEmailListID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_DeleteEmailList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_DeleteEmailList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetEmailAddressByEmailListID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetEmailAddressByEmailListID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetEmailAddressByUserID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetEmailAddressByUserID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetEmailListByUserID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetEmailListByUserID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertEmailAddress]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertEmailAddress]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertEmailAndList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertEmailAndList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertEmailList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertEmailList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertQueuedEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertQueuedEmail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertSentEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertSentEmail]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateEmailAccount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateEmailAccount]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateEmailList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateEmailList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateQueuedEMailState]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateQueuedEMailState]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_DeleteEmail
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@EmailID	integer
)
AS
	DELETE EmailAndList WHERE EmailID=@EmailID;
	DELETE EmailAddress WHERE EmailID=@EmailID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_DeleteEmailAndList
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@EmailListID	integer,
	@EmailID	integer
)
AS
	DELETE EmailAndList WHERE EmailListID=@EmailListID AND EmailID=@EmailID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_DeleteEmailAndListByEmailListID
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@EmailListID	integer
)
AS
	DELETE EmailAndList WHERE EmailListID=@EmailListID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_DeleteEmailList
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@EmailListID	integer
)
AS
	DELETE EmailAndList WHERE EmailListID=@EmailListID;
	DELETE EmailList WHERE EmailListID=@EmailListID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetEmailAddressByEmailListID
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@EmailListID 	int
)
 AS

	SELECT * FROM EmailAddress WHERE EmailID IN (SELECT EmailID FROM EmailAndList WHERE EmailListID=@EmailListID)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetEmailAddressByUserID
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@UserID 	int
)
 AS

	SELECT * FROM EmailAddress WHERE UserID=@UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_GetEmailListByUserID
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	30/6/06
*/
(
	@UserID 	int
)
 AS

	SELECT * FROM EmailList WHERE UserID=@UserID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [sp_InsertEmailAddress]
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	10/10/06
*/
(
	@UserID		integer,
	@EmailAddress	nvarchar(50),
	@ReturnID	int=-1	output 
)
AS 
	INSERT INTO EmailAddress (UserID, EmailAddress, CreateDate)
	values (@UserID, @EmailAddress, getdate());

	SELECT @ReturnID=@@IDENTITY;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [sp_InsertEmailAndList]
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	10/10/06
*/
(
	@EmailListID		integer,
	@EmailID		integer

)
AS 
	INSERT INTO EmailAndList (EmailListID, EmailID)
	values (@EmailListID, @EmailID);
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [sp_InsertEmailList]
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	10/10/06
*/
(
	@UserID		integer,
	@ListName	nvarchar(50),
	@ReturnID	int=-1	output 
)
AS 
	INSERT INTO EmailList (UserID, ListName, CreateDate)
	values (@UserID, @ListName, getdate());

	SELECT @ReturnID=@@IDENTITY;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [sp_InsertQueuedEmail]
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	10/10/06
*/
(
	@UserID		integer,
	@From		nvarchar(50),
	@To		nvarchar(50),
	@Subject	nvarchar(500),
	@Body		ntext,
	@ReturnID	int=-1	output 
)
AS 
	INSERT INTO QueuedEmail (UserID, [From], [To], Subject, Body, CreateDate)
	values (@UserID, @From, @To, @Subject, @Body, getdate());

	SELECT @ReturnID=@@IDENTITY;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE [sp_InsertSentEmail]
/*
	Mult-purpose Article Search Capability	
	Version 0.6
	Coded By Zhang Xiaohua
	10/10/06
*/
(
	@UserID			integer,
	@FromEmailAddress	nvarchar(50),
	@ToEmailAddress		nvarchar(50)
)
AS 
	INSERT INTO SentEmail (UserID, FromEmailAddress, ToEmailAddress, SentDate)
	values (@UserID, @FromEmailAddress, @ToEmailAddress, getdate());
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

 CREATE PROCEDURE [sp_UpdateEmailAccount] 
(
	@EmailAccount		varchar(100),
	@AccountPassword	varchar(50),
	@SmtpServer		varchar(50),
	@LogDir			varchar(100),
	@EmailListSize		integer,
	@EmailSize		integer,
	@State			char(1)
)
AS
	SELECT * from EmailAccount
	IF @@ROWCOUNT = 0
 		BEGIN
			INSERT INTO EmailAccount (EmailAccount, AccountPassword, SmtpServer, LogDir, EmailListSize, EmailSize, State)
			VALUES (@EmailAccount, @AccountPassword, @SmtpServer, @LogDir, @EmailListSize, @EmailSize, @State);
		END
	ELSE
 		BEGIN
		SET NOCOUNT OFF;
			UPDATE EmailAccount SET 
				EmailAccount=@EmailAccount,
				AccountPassword=@AccountPassword,
				SmtpServer=@SmtpServer,
				LogDir=@LogDir,
				EmailListSize=@EmailListSize,
				EmailSize=@EmailSize,
				State=@State;
 		END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

 CREATE PROCEDURE [sp_UpdateEmailList] 
(
	@EmailListID		integer,
	@ListName		nvarchar(50)
)
	AS
	SET NOCOUNT OFF;
	UPDATE EmailList SET 
			ListName=@ListName
	 WHERE EmailListID=@EmailListID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

 CREATE PROCEDURE [sp_UpdateQueuedEMailState] 
(
	@QueuedEMailID		integer,
	@State			char(1)
)
	AS
	SET NOCOUNT OFF;
	UPDATE QueuedEMail SET 
			State=@State
	 WHERE QueuedEMailID=@QueuedEMailID;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

